package com.pur.zsms.supplier.dao.impl;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.matermanage.mater.po.MaterLevelPo;
import com.pur.zsms.matermanage.mater.vo.MaterVo;
import com.pur.zsms.matermanage.mater.vo.MaterVoForCostSupplier;
import com.pur.zsms.po.DistributorSupplierMaterPo;
import com.pur.zsms.po.SupplierShopUserPo;
import com.pur.zsms.storemanage.po.StorePo;
import com.pur.zsms.supplier.dao.SupplierDao;
import com.pur.zsms.supplier.po.ShopSupplierTypePo;
import com.pur.zsms.supplier.po.SupplierDistributeMaterPo;
import com.pur.zsms.supplier.po.SupplierDistributePo;
import com.pur.zsms.supplier.po.SupplierPo;
import com.pur.zsms.supplier.po.SupplierQualificationPo;
import com.pur.zsms.supplier.po.SupplierUserPo;
import com.pur.zsms.supplier.vo.ShopSupplierTypeVo;
import com.pur.zsms.supplier.vo.ShopSupplierVo;
import com.pur.zsms.supplier.vo.SupplierDistributeMateVo;
import com.pur.zsms.supplier.vo.SupplierDistributeVo;
import com.pur.zsms.supplier.vo.SupplierOrderCustVo;
import com.pur.zsms.supplier.vo.SupplierQualificationVo;
import com.pur.zsms.supplier.vo.SupplierUserVo;
import com.pur.zsms.supplier.vo.SupplierVo;
import com.pur.zsms.systemsetting.datadictionary.po.Code;
import com.pur.zsms.vo.DistributorSupplierMaterVo;

@Repository("supplierDao")
public class SupplierDaoImpl extends GenericDao implements SupplierDao {

	@Override
	public void addSupplier(SupplierPo c) throws GenericException {
		this.persist(c);
	}

	public void addSupplierUserPo(SupplierUserPo c) throws GenericException {
		this.persist(c);
	}

	public void addSupplierShopUserPo(SupplierShopUserPo c) throws GenericException {
		this.persist(c);
	}

	public void updateSupplierShopUserPo(SupplierShopUserPo c) throws GenericException {
		this.merge(c);
	}

	public void addSupplierDistributeMaterPo(SupplierDistributeMaterPo c) throws GenericException {
		this.persist(c);
	}

	public void updateSupplierDistributeMaterPo(SupplierDistributeMaterPo c) throws GenericException {
		this.merge(c);
	}

	public void addSupplierQualificationPo(SupplierQualificationPo c) throws GenericException {
		this.persist(c);
	}

	@Override
	public void addSupplierDistribute(SupplierDistributePo c) throws GenericException {
		this.persist(c);
	}

	public void addDistributorSupplierMaterPo(DistributorSupplierMaterPo c) throws GenericException {
		this.persist(c);
	}

	@Override
	public void addShopSupplierTypePo(ShopSupplierTypePo c) throws GenericException {
		this.persist(c);

	}

	@Override
	public void updateSupplier(SupplierPo c) throws GenericException {
		this.merge(c);
	}

	@Override
	public void deleteSupplier(SupplierPo c) throws GenericException {
		this.remove(c);
	}

	public void deleteDistributorSupplierMaterPo(DistributorSupplierMaterPo c) throws GenericException {
		this.remove(c);
	}

	@Override
	public void deleteShopSupplierTypePo(int shopId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete ShopSupplierTypePo o where o.shopId=" + shopId + " and o.supId = " + supId);
		template.executeUpdate(ql.toString());

		StringBuffer ql2 = new StringBuffer();
		ql2.append(" delete SupplierShopPo o where o.shopId=" + shopId + " and o.supId = " + supId);
		template.executeUpdate(ql2.toString());

	}

	public void deleteSupplierDistributeMaterPo(int materId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete SupplierDistributeMaterPo o where o.materId=" + materId + " and o.supId = " + supId);
		template.executeUpdate(ql.toString());
	}

	public void deleteSupplierDistribute(long disId, int supId, String disDate) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete SupplierDistributePo o where o.disId='" + disId + "' AND o.supId = '" + supId + "' AND o.disDate = '" + disDate + "'");
		template.executeUpdate(ql.toString());
	}

	@Override
	public PaginationSupport listSupplier(PageController pageController, SupplierVo supplierVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT o.sup_id, o.sup_companyname, o.sup_countryno, o.sup_cityno, o.sup_phone, o.sup_faxcountryno, o.sup_faxcityno, o.sup_faxphone, o.sup_mobile, o.sup_zip, o.sup_province, o.sup_city, o.sup_country, o.sup_street, o.sup_webSite, o.sup_desc, o.sup_isactive, o.sup_inserttime, o.sup_updatetime, o.user_id,(SELECT GROUP_CONCAT( su.user_id, '-', u.real_name, '<br/>' SEPARATOR '' ) FROM t_user u, t_supplier_user su WHERE su.user_id = u.user_id AND su.sup_id = o.sup_id GROUP BY su.sup_id ) FROM t_supplier o, t_user u WHERE u.user_id = o.user_id  ");
		if (supplierVo.getCompanyId() != 0) {
			ql.append(" and o.company_id ='" + supplierVo.getCompanyId() + "'");
		}

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findDistributeMater(PageController pageController, SupplierDistributeMateVo supplierDistributeMateVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT s.ord_cust_id, (SELECT shop_name FROM t_shop WHERE shop_id = s.ord_cust_id) AS shopname, o.ord_mater_id, SUM(o.ord_mater_weight), o.leave_message, m.mater_name, m.unit, m.specifications,m.brands FROM t_order_supplier_mater o, t_order_supplier s, t_material m WHERE o.ord_id = s.ord_id AND o.ord_sup_id = '"
				+ supplierDistributeMateVo.getSupId()
				+ "' AND s.ord_sup_id = o.ord_sup_id AND s.ord_date = '"
				+ supplierDistributeMateVo.getOrdDate() + "' ");

		if (supplierDistributeMateVo.getMaterId() != 0) {
			ql.append(" AND o.ord_mater_id = '" + supplierDistributeMateVo.getMaterId() + "' ");
		}
		if (supplierDistributeMateVo.getMaterName() != null && !"".equalsIgnoreCase(supplierDistributeMateVo.getMaterName())) {
			ql.append(" AND m.mater_name = '" + supplierDistributeMateVo.getMaterName() + "' ");
		}

		ql.append(" AND m.mater_id = o.ord_mater_id GROUP BY o.ord_mater_id, s.ord_cust_id, o.leave_message ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findDistributeMaterForGather(PageController pageController, SupplierDistributeMateVo supplierDistributeMateVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT m.mater_id, m.mater_type, m.mater_name, m.unit, m.brands, m.specifications, dm.order_mater FROM t_supplier_distribute_mater dm, t_material m WHERE m.mater_id = dm.mater_id AND dm.sup_id = '"
				+ supplierDistributeMateVo.getSupId() + "'  order by dm.order_mater");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public int findDistributeMater(SupplierDistributeMateVo supplierDistributeMateVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT s.ord_cust_id, (SELECT shop_name FROM t_shop WHERE shop_id = s.ord_cust_id) AS shopname, o.ord_mater_id, SUM(o.ord_mater_weight), o.leave_message, m.mater_name, m.unit, m.specifications,m.brands FROM t_order_supplier_mater o, t_order_supplier s, t_material m WHERE o.ord_id = s.ord_id AND o.ord_sup_id = '"
				+ supplierDistributeMateVo.getSupId()
				+ "' AND s.ord_sup_id = o.ord_sup_id AND s.ord_date = '"
				+ supplierDistributeMateVo.getOrdDate() + "' ");

		if (supplierDistributeMateVo.getMaterId() != 0) {
			ql.append(" AND o.ord_mater_id = '" + supplierDistributeMateVo.getMaterId() + "' ");
		}
		if (supplierDistributeMateVo.getMaterName() != null && !"".equalsIgnoreCase(supplierDistributeMateVo.getMaterName())) {
			ql.append(" AND m.mater_name = '" + supplierDistributeMateVo.getMaterName() + "' ");
		}

		ql.append(" AND m.mater_id = o.ord_mater_id GROUP BY o.ord_mater_id, s.ord_cust_id, o.leave_message ");
		List list = this.entityManager.createNativeQuery(ql.toString()).getResultList();
		return list.size();
	}

	public PaginationSupport findCustForOrder(PageController pageController, SupplierOrderCustVo supplierVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT u.shop_id,u.shop_name FROM t_shop u WHERE EXISTS (SELECT 1 FROM t_order_supplier s WHERE s.`ord_cust_id` = u.shop_id AND s.`ord_sup_id`='"
				+ supplierVo.getSupId() + "' AND s.`ord_date`= '" + supplierVo.getOrdDate() + "')");

		ql.append("AND NOT EXISTS ( SELECT 1 FROM t_material_level_bl m WHERE m.mater_day ='" + supplierVo.getOrdDate()
				+ "' AND m.cust_id = u.shop_id  AND m.`sup_id` = '" + supplierVo.getSupId() + "')");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findSupDistribute(PageController pageController, SupplierDistributeVo supplierDistributeVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT d.dis_id,d.sup_id,s.sup_companyname, GROUP_CONCAT(u.shop_name), d.dis_date FROM t_supplier_distribute d, t_shop u ,t_supplier s WHERE  s.sup_id = d.sup_id and  d.cust_id = u.shop_id AND d.sup_id = '"
				+ supplierDistributeVo.getSupId() + "' AND d.dis_date = '" + supplierDistributeVo.getDisDate() + "' GROUP BY d.dis_id ");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findSupplierQualification(PageController pageController, SupplierQualificationVo supplierQualificationVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT o.`qid`,o.`q_name`,o.`q_validity_dates`,o.`sup_id`,s.`sup_companyname` FROM  t_supplier_qualification o  ,t_supplier s WHERE o.`sup_id`=s.`sup_id` and o.sup_id ='"
				+ supplierQualificationVo.getSupId() + "'");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public PaginationSupport listSupCus(PageController pageController, ShopSupplierVo suppCusVo) throws GenericException {

		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT o.shop_spplier_id, o.shop_id, o.sup_id, GROUP_CONCAT(d.describ,'   '), b.shop_name, c.sup_companyname FROM t_shop_supplier_type o, t_shop b, t_supplier c, t_code d WHERE b.shop_id = o.shop_id AND o.sup_id = c.sup_id AND o.sup_type = d.code_oder AND d.code_type_id = 'mater_type'  ");
		if (suppCusVo != null && suppCusVo.getSupId() != 0) {
			ql.append(" and o.sup_id ='" + suppCusVo.getSupId() + "'  ");
		}
		if (suppCusVo != null && suppCusVo.getShopName() != null && !suppCusVo.getShopName().equals("")) {
			ql.append(" and b.shop_name like'%" + suppCusVo.getShopName() + "%'  ");
		}
		ql.append(" GROUP BY o.shop_id,o.sup_id ");
		return this.findByNativeQuery(ql.toString(), pageController);

	}

	@Override
	public SupplierPo findSupplierById(int id) throws GenericException {
		return this.entityManager.find(SupplierPo.class, id);
	}

	@Override
	public List<SupplierPo> getAllSupPo() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierPo o ");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<SupplierDistributeMaterPo> getSupplierDistributeMaterPoByMaterIdSupId(int materId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierDistributeMaterPo o where o.materId ='" + materId + "' and o.supId ='" + supId + "'");
		List<SupplierDistributeMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<SupplierDistributeMaterPo> getSupplierDistributeMaterPoBySupId(int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierDistributeMaterPo o where   o.orderMater !=0 and o.supId ='" + supId + "' order by o.orderMater");
		List<SupplierDistributeMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<SupplierPo> getAllSupPoCust() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierPo o ");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<SupplierPo> getSupPoByUserId(int userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierPo o  where o.userId ='" + userId + "'");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public SupplierPo getSupPoByUserId(long userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT o FROM SupplierPo o WHERE o.supplierId = ( SELECT s.supId FROM SupplierUserPo s WHERE s.userId = '" + userId + "')");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	public List<SupplierUserPo> getSupplierUserPo(SupplierUserVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT o FROM SupplierUserPo o WHERE 1=1 ");
		if (c.getUserId() != 0) {
			ql.append(" AND o.userId = '" + c.getUserId() + "'");
		}
		if (c.getSupId() != 0) {
			ql.append(" AND o.supId = '" + c.getSupId() + "'");
		}

		List<SupplierUserPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public SupplierUserPo getSupUserPoByUserId(long userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierUserPo o where o.userId = '" + userId + "'");
		List<SupplierUserPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	public StorePo getSupStoreHouseIdByUserId(long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from StorePo o where o.supId = '" + supId + "'");
		List<StorePo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	@Override
	public SupplierPo getSupPoBySupId(String supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierPo o where o.supplierId = '" + supId + "'");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	public List<SupplierPo> getSupPoAll() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierPo o ");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	public SupplierQualificationPo getSupplierQualificationPoByQid(int qid) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierQualificationPo o where o.qid = '" + qid + "'");
		List<SupplierQualificationPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	public List<SupplierPo> getSupPoByName(String supName) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from SupplierPo o where o.companyName = '" + supName + "'");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<ShopSupplierTypePo> getSuppCusPo(ShopSupplierVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopSupplierTypePo o where 1=1 ");
		if (c != null) {
			if (c.getSupId() != 0) {
				ql.append(" and o.supId = '" + c.getSupId() + "' ");
			}
			if (c.getShopId() != 0) {
				ql.append(" and o.shopId = '" + c.getShopId() + "' ");
			}
		}

		List<ShopSupplierTypePo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<ShopSupplierTypePo> getShopSupplierTypePo(ShopSupplierTypeVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopSupplierTypePo o where 1=1 ");
		if (c != null) {
			if (c.getSupId() != 0) {
				ql.append(" and o.supId = '" + c.getSupId() + "' ");
			}
			if (c.getShopId() != 0) {
				ql.append(" and o.shopId = '" + c.getShopId() + "' ");
			}
		}

		List<ShopSupplierTypePo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<ShopSupplierTypePo> getSuppCusPoByTypeCheck(ShopSupplierVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopSupplierTypePo o where 1=1 ");
		if (c != null) {
			if (c.getSupId() != 0) {
				ql.append(" and o.supId = '" + c.getSupId() + "' ");
			}
			if (c.getShopId() != 0) {
				ql.append(" and o.shopId = '" + c.getShopId() + "' ");
			}
			if (c.getSupType() != 0) {
				ql.append(" and o.supType = '" + c.getSupType() + "' ");
			}

		}

		List<ShopSupplierTypePo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<Object[]> getAllTotalByDate(String date, int supId, int custId, String custIdAll, String orderBy) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  a.ord_mater_id,  a.ord_mater_level,  a.ord_sup_id,  SUM(a.ord_mater_weight) ,  c.mater_name , d.sup_companyname,e.describ,c.unit,c.brands,c.specifications,(SELECT  ml.mater_value_undressed FROM t_material_level ml WHERE ml.`sup_id` = a.`ord_sup_id`  AND ml.`mater_id` = c.`mater_id` "
				+ " AND ml.cust_id ='"
				+ custId
				+ "' "
				+ ")  FROM  t_order_supplier_mater a,  t_order_supplier b ,  t_material c ,t_supplier d,t_code e WHERE d.sup_id =a.ord_sup_id and  a.ord_id = b.ord_id  AND b.ord_date = '"
				+ date
				+ "'  AND c.mater_id =a.ord_mater_id  AND a.ord_sup_id = '"
				+ supId
				+ "'  AND b.ord_sup_id = '"
				+ supId
				+ "' AND e.`code_oder`= c.`mater_type` AND e.`code_type_id`='mater_type' ");
		if (custIdAll != null && !custIdAll.equals("")) {
			sql.append(" AND b.`ord_cust_id` IN (" + custIdAll + ")");
		}
		sql.append(" GROUP BY a.ord_mater_id,a.ord_mater_level ");
		sql.append(" ORDER BY " + orderBy + "");

		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getAllTotalByDate(String date, int supId, String custIdAll, String orderBy) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  a.ord_mater_id,  a.ord_mater_level,  a.ord_sup_id,  SUM(a.ord_mater_weight) ,  c.mater_name , d.sup_companyname,e.describ,c.unit,c.brands,c.specifications, "

				+ "(SELECT bb.mater_value_undressed FROM (SELECT MAX(mater_day) AS mday, mater_id FROM t_material_level_cbl WHERE sup_id = '"
				+ supId
				+ "' GROUP BY mater_id) AS aa, t_material_level_cbl bb WHERE aa.mater_id = bb.mater_id AND bb.mater_id =c.mater_id AND aa.mday = bb.mater_day AND bb.sup_id ='"
				+ supId
				+ "') "

				+ " FROM  t_order_supplier_mater a,  t_order_supplier b ,  t_material c ,t_supplier d,t_code e WHERE d.sup_id =a.ord_sup_id and  a.ord_id = b.ord_id  AND b.ord_date = '"
				+ date + "'  AND c.mater_id =a.ord_mater_id  AND a.ord_sup_id = '" + supId + "'  AND b.ord_sup_id = '" + supId + "'");
		if (custIdAll != null && !custIdAll.equals("")) {
			sql.append("  AND b.ord_cust_id IN(" + custIdAll + ")");

		}
		sql.append(" AND e.`code_oder`= c.`mater_type` AND e.`code_type_id`='mater_type' ");
		sql.append(" GROUP BY a.ord_mater_id,a.ord_mater_level ");
		sql.append(" ORDER BY " + orderBy + "");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getAllTotalByDate(String date, int supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  a.ord_mater_id,  a.ord_mater_level,  a.ord_sup_id,  SUM(a.ord_mater_weight) ,  c.mater_name , d.sup_companyname,e.describ,c.unit,c.brands,c.specifications, "

				+ "(SELECT bb.mater_value_undressed FROM (SELECT MAX(mater_day) AS mday, mater_id FROM t_material_level_cbl WHERE sup_id = '"
				+ supId
				+ "' GROUP BY mater_id) AS aa, t_material_level_cbl bb WHERE aa.mater_id = bb.mater_id AND bb.mater_id =c.mater_id AND aa.mday = bb.mater_day AND bb.sup_id ='"
				+ supId
				+ "') "

				+ " FROM  t_order_supplier_mater a,  t_order_supplier b ,  t_material c ,t_supplier d,t_code e WHERE d.sup_id =a.ord_sup_id and  a.ord_id = b.ord_id  AND b.ord_date = '"
				+ date
				+ "'  AND c.mater_id =a.ord_mater_id  AND a.ord_sup_id = '"
				+ supId
				+ "'  AND b.ord_sup_id = '"
				+ supId
				+ "' AND e.`code_oder`= c.`mater_type` AND e.`code_type_id`='mater_type' ");
		sql.append(" GROUP BY a.ord_mater_id,a.ord_mater_level ORDER BY e.`describ`");

		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getCustQuotationDate(int supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("select  m.mater_id , t2.mater_day, (SELECT describ FROM t_code WHERE code_type_id = 'mater_type' AND t_code.code_oder = m.mater_type) AS matertype, m.brands, m.specifications, m.mater_name, t2.mater_value, m.unit,m.mater_type FROM t_material m LEFT JOIN (SELECT mc2.* FROM (SELECT MAX(mc.mater_day) AS a1, mc.mater_id AS a2 FROM t_material_level_cbl mc WHERE mc.sup_id = '"
				+ supId
				+ "' GROUP BY mc.mater_id) AS t1, t_material_level_cbl mc2 WHERE mc2.mater_id = t1.a2 AND mc2.mater_day = t1.a1 ) t2 ON t2.mater_id = m.mater_id ORDER BY matertype");

		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getAllTotalByUserIdALl(String date, int supId, String custIdAll) throws GenericException {
		List list = this.entityManager
				.createNativeQuery(
						"  SELECT  a.ord_mater_id,  a.ord_mater_level,  a.ord_sup_id,  SUM(a.ord_mater_weight) ,  CONCAT(LEFT(c.`all_spell`,1),'-', c.mater_name) , d.sup_companyname,e.describ,c.unit,c.brands,c.specifications  FROM  t_order_supplier_mater a,  t_order_supplier b ,  t_material c ,t_supplier d,t_code e WHERE d.sup_id =a.ord_sup_id and  a.ord_id = b.ord_id  AND b.ord_date = '"
								+ date
								+ "'  AND c.mater_id =a.ord_mater_id  AND a.ord_sup_id = '"
								+ supId
								+ "'  AND b.ord_sup_id = a.ord_sup_id AND e.`code_oder`= c.`mater_type` AND e.`code_type_id`='mater_type' "
								+ "AND b.`ord_cust_id` IN ("
								+ custIdAll
								+ ")"
								+ " GROUP BY a.ord_mater_id,a.ord_mater_level ORDER BY e.`describ`,c.`all_spell`").getResultList();

		return list;
	}

	public List<Object[]> getSupDistributeSummarySheet(String date, int supId, String materType, int storeHouseId, String shopIdAll)
			throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT (SELECT `code_name` FROM t_code c WHERE c.`code_type_id`='mater_type' AND c.`code_oder`= m.`mater_type`) as materType,sm.ord_mater_id, m.mater_name, m.specifications, m.unit, REPLACE(GROUP_CONCAT(sm.ord_mater_weight,'(',(SELECT  ss.code   FROM t_supplier_shop ss   WHERE ss.shop_id = s.`ord_cust_id` AND ss.sup_id = s.`ord_sup_id`),'号)' SEPARATOR '+' ),'.00','') AS materWeightF, SUM(ord_mater_weight),(SELECT  st.`mater_number`  FROM  t_store_material st  WHERE st.`mater_id` = m.mater_id  AND st.store_house_id = '"
				+ storeHouseId
				+ "') ,sm.`ord_mater_level` FROM t_order_supplier_mater sm, t_order_supplier s , t_material m WHERE s.ord_id = sm.ord_id AND s.ord_sup_id = sm.ord_sup_id AND s.ord_date = '"
				+ date
				+ "' AND m.mater_id=sm.ord_mater_id AND s.`ord_sup_id`='"
				+ supId
				+ "' AND m.`mater_type` = "
				+ materType
				+ " AND NOT EXISTS ( SELECT 1 FROM t_supplier_distribute_mater dm WHERE dm.`sup_id`= s.`ord_sup_id` AND dm.`mater_id` =m.`mater_id` AND dm.`order_mater` != 0 ) ");

		if (!"".equals(shopIdAll) && !"0".equals(shopIdAll) && shopIdAll != null) {
			sql.append(" AND EXISTS( SELECT 1 FROM t_shop o WHERE o.shop_id =s.`ord_cust_id` AND o.`shop_id`IN (" + shopIdAll + ") )");
		}
		sql.append(" GROUP BY ord_mater_id ORDER BY materType,m.mater_name");

		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getSupDistributeSummarySheetForGroupbyMaterType(String date, int supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT m.mater_type, (SELECT describ FROM t_code c WHERE c.code_type_id = 'mater_type' AND c.code_oder = m.mater_type) , (SELECT code_id+0 FROM t_code c WHERE c.code_type_id = 'mater_type' AND c.code_oder = m.mater_type) AS codeId FROM t_order_supplier_mater sm, t_order_supplier s, t_material m WHERE s.ord_id = sm.ord_id AND s.ord_sup_id = sm.ord_sup_id AND s.ord_date = '"
				+ date
				+ "' AND m.mater_id = sm.ord_mater_id AND s.ord_sup_id = '"
				+ supId
				+ "' AND NOT EXISTS ( SELECT 1 FROM t_supplier_distribute_mater dm WHERE dm.`sup_id`= s.`ord_sup_id` AND dm.`mater_id` =m.`mater_id` AND dm.`order_mater` != 0 ) GROUP BY m.mater_type ORDER BY codeId");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getSupDistributeSummarySheetForMaterOrder(String date, int supId, int materId, int storeHouseId, String shopIdAll)
			throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT (SELECT `code_name` FROM t_code c WHERE c.`code_type_id`='mater_type' AND c.`code_oder`= m.`mater_type`) as materType,sm.ord_mater_id, m.mater_name, m.specifications, m.unit, REPLACE(GROUP_CONCAT(sm.ord_mater_weight ,'(',(SELECT  ss.code   FROM t_supplier_shop ss   WHERE ss.shop_id = s.`ord_cust_id` AND ss.sup_id = s.`ord_sup_id`),'号)' SEPARATOR '+' ),'.00','' ) AS materWeightF, SUM(ord_mater_weight),(SELECT st.`mater_number` FROM t_store_material st WHERE st.`mater_id`= m.mater_id AND st.store_house_id='"
				+ storeHouseId
				+ "'),sm.`ord_mater_level` FROM t_order_supplier_mater sm, t_order_supplier s , t_material m WHERE s.ord_id = sm.ord_id AND s.ord_sup_id = sm.ord_sup_id AND s.ord_date = '"
				+ date + "' AND m.mater_id=sm.ord_mater_id AND s.`ord_sup_id`='" + supId + "' And m.mater_id ='" + materId + "' ");
		if (!"".equals(shopIdAll) && !"0".equals(shopIdAll) && shopIdAll != null) {
			sql.append(" AND EXISTS( SELECT 1 FROM t_shop o WHERE o.shop_id =s.`ord_cust_id` AND o.`shop_id`IN (" + shopIdAll + ") )");
		}
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object> getSupDistributeSummarySheetLeaveMessageForMaterOrder(String date, int supId, int materId, String shopIdAll)
			throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT GROUP_CONCAT( sm.leave_message, '(', (SELECT ss.code FROM t_supplier_shop ss WHERE ss.shop_id = s.ord_cust_id AND ss.sup_id = s.ord_sup_id), '号)' SEPARATOR '+' ) FROM t_order_supplier_mater sm, t_order_supplier s, t_material m WHERE s.ord_id = sm.ord_id AND s.ord_sup_id = sm.ord_sup_id AND s.ord_date = '"
				+ date
				+ "' AND m.mater_id = sm.ord_mater_id AND s.ord_sup_id = '"
				+ supId
				+ "' AND m.mater_id = '"
				+ materId
				+ "' AND sm.leave_message !='' AND sm.leave_message IS NOT NULL  ");
		if (!"".equals(shopIdAll) && !"0".equals(shopIdAll) && shopIdAll != null) {
			sql.append(" AND EXISTS( SELECT 1 FROM t_shop o WHERE o.shop_id =s.`ord_cust_id` AND o.`shop_id`IN (" + shopIdAll + ") )");
		}
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object[]> getSupDistributLeaveMessage(String date, int supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT (SELECT c.describ FROM t_code c WHERE c.code_type_id = 'mater_type' AND c.code_oder = m.mater_type) AS materType, sm.ord_mater_id, m.mater_name, m.specifications,sm.`ord_mater_weight`, m.unit, (SELECT  sh.code FROM t_supplier_shop sh  WHERE sh.shop_id = s.ord_cust_id AND sh.sup_id =s.`ord_sup_id`), sm.leave_message, s.ord_cust_id,(SELECT  sh.shop_name FROM t_shop sh  WHERE sh.shop_id = s.ord_cust_id ) FROM t_order_supplier_mater sm, t_order_supplier s, t_material m WHERE s.ord_id = sm.ord_id AND s.ord_sup_id = sm.ord_sup_id AND s.ord_date = '"
				+ date
				+ "' AND m.mater_id = sm.ord_mater_id AND s.ord_sup_id = '"
				+ supId
				+ "' AND sm.leave_message !='' AND sm.leave_message IS NOT NULL ORDER BY s.ord_cust_id, materType");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object> getMaxSupplierDistributeDisId(String disDate, long supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT IFNULL(MAX(dis_id)+1,1) FROM t_supplier_distribute WHERE sup_id = '" + supId + "' AND dis_date = '" + disDate + "'");

		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object[]> getExcelCust(String date, int supId) throws GenericException {

		List list = this.entityManager.createNativeQuery(
				"SELECT DISTINCT b.ord_cust_id,c.shop_name FROM t_order_supplier_mater a, t_order_supplier b,t_shop c WHERE a.ord_id = b.ord_id AND b.ord_date = '"
						+ date + "' AND a.ord_sup_id = '" + supId + "' AND b.ord_cust_id=c.shop_id ORDER BY b.`ord_cust_id` ASC").getResultList();

		return list;
	}

	public List<Object[]> getExcelCust(String date, int supId, String custIdAll) throws GenericException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT b.ord_cust_id,c.shop_name FROM t_order_supplier_mater a, t_order_supplier b,t_shop c WHERE a.ord_id = b.ord_id AND b.ord_date = '"
				+ date + "' AND a.ord_sup_id = '" + supId + "' AND b.ord_cust_id=c.shop_id ");
		if (custIdAll != null && !custIdAll.equals("")) {
			sql.append(" AND b.`ord_cust_id` IN(" + custIdAll + ")");
		}
		sql.append(" ORDER BY b.`ord_cust_id` ASC");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getExcelCustForDistribute(String userIdAll) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT shop_id ,shop_name FROM t_shop WHERE shop_id IN (" + userIdAll + ") ORDER BY shop_id");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getExcelCustAllRealMaterByDateForPrint(String date, int supId, String custId) throws GenericException {

		List list = this.entityManager
				.createNativeQuery(
						" SELECT a.ord_mater_id, a.ord_mater_level, a.ord_sup_id, SUM(a.ord_mater_weight), c.mater_name,c.unit,c.specifications,FORMAT(d.`mater_value_undressed`*1000,2),a.`ord_mater_value` ,(SELECT describ FROM t_code WHERE code_type_id = 'mater_type' AND code_oder = c.mater_type) as matertype,e.`ord_mater_weight` FROM t_order_supplier_mater_reality a, t_order_supplier b, t_material c,t_material_level_bl d,t_order_supplier_mater e WHERE a.ord_id = b.ord_id AND b.ord_date = '"
								+ date
								+ "' AND c.mater_id = a.ord_mater_id AND a.ord_sup_id = '"
								+ supId
								+ "' AND b. ord_cust_id = '"
								+ custId
								+ "' AND b. ord_sup_id = '"
								+ supId
								+ "' AND e.`ord_id`=a.`ord_id` AND e.`ord_mater_id` = a.`ord_mater_id` AND e.`ord_sup_id` = a.`ord_sup_id`  AND d.`mater_id`=a.`ord_mater_id` AND d.`sup_id` = a.`ord_sup_id` AND d.`mater_day` = b.ord_date AND d.`cust_id`=b.`ord_cust_id` GROUP BY a.ord_mater_id, a.ord_mater_level  order by matertype")
				.getResultList();

		return list;
	}

	public List<Object[]> getExcelCustAllRealMaterByDateForPrintCost(String date, int supId, String custId) throws GenericException {

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.ord_mater_id, a.ord_mater_level, a.ord_sup_id, SUM(a.ord_mater_weight), c.mater_name,c.unit,c.specifications,(SELECT describ FROM t_code WHERE code_type_id = 'mater_type' AND code_oder = c.`mater_type` ) as matertype FROM t_order_supplier_mater_reality a, t_order_supplier b, t_material c WHERE a.ord_id = b.ord_id AND b.ord_date = '"
				+ date
				+ "' AND c.mater_id = a.ord_mater_id AND a.ord_sup_id = '"
				+ supId
				+ "' AND b. ord_cust_id = '"
				+ custId
				+ "' AND b. ord_sup_id = '" + supId + "' GROUP BY a.ord_mater_id, a.ord_mater_level  order by matertype");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object[]> getExcelCustAllRealMaterByDate(String date, int supId, String custId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.ord_mater_id, a.ord_mater_level, a.ord_sup_id, SUM(a.ord_mater_weight), c.mater_name,c.unit,c.specifications,a.`leave_message`,(SELECT d.`describ` FROM t_code d WHERE d.code_type_id ='mater_type' AND d.code_oder = c.`mater_type`) FROM t_order_supplier_mater a, t_order_supplier b, t_material c WHERE a.ord_id = b.ord_id AND b.ord_date = '"
				+ date
				+ "' AND c.mater_id = a.ord_mater_id AND a.ord_sup_id = '"
				+ supId
				+ "' AND b. ord_cust_id = '"
				+ custId
				+ "' AND b. ord_sup_id = '" + supId + "' GROUP BY c.`mater_type`,a.ord_mater_id, a.ord_mater_level ");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getExcelCustTotalRealMaterByDateMaterIdMaterLevel(String date, int supId, String custId, String materId, String materLevel)
			throws GenericException {

		List list = this.entityManager
				.createNativeQuery(
						" SELECT SUM(a.ord_mater_weight),a.`leave_message` FROM t_order_supplier_mater a, t_order_supplier b, t_material c WHERE a.ord_id = b.ord_id AND b.ord_date = '"
								+ date
								+ "' AND c.mater_id = a.ord_mater_id AND a.ord_sup_id = '"
								+ supId
								+ "' AND b. ord_cust_id = '"
								+ custId
								+ "' AND b. ord_sup_id = '"
								+ supId
								+ "' AND a.ord_mater_id = '"
								+ materId
								+ "' AND a.ord_mater_level = '"
								+ materLevel + "' GROUP BY a.ord_mater_id, a.ord_mater_level ").getResultList();

		return list;
	}

	@Override
	public List<MaterLevelPo> getMaterLevelPoByMaterIdSupId(int materId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from MaterLevelPo o where o.materId = '" + materId + "' and o.supId = '" + supId + "'");
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<Code> findCodes() throws GenericException {
		return this.entityManager.createQuery("from Code o where o.codeTypeId='mater_type'").getResultList();
	}

	public List<Object[]> getUserIdDistributePoList(String date, int supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT GROUP_CONCAT(d.cust_id) FROM t_supplier_distribute d WHERE d.sup_id = '" + supId + "' AND d.dis_date = '" + date
				+ "' GROUP BY d.dis_id");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public void deleteSupplierQualification(int qid) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete SupplierQualificationPo o where o.qid=" + qid);
		template.executeUpdate(ql.toString());
	}

	public PaginationSupport listSupplierCost(PageController pageController, MaterVoForCostSupplier tMaterVo) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  a.ord_mater_id,  a.ord_mater_level,  0,  SUM(a.ord_mater_weight) ,  c.mater_name , 1,(SELECT describ FROM t_code WHERE code_type_id = 'mater_type' AND code_oder =c.mater_type),c.unit,c.brands,c.specifications, a.ord_mater_value_cost_unit"
				+ " FROM  t_order_supplier_mater_reality a,  t_order_supplier b ,  t_material c  WHERE  a.ord_id = b.ord_id  AND b.ord_date = '"
				+ tMaterVo.getDate()
				+ "'  AND c.mater_id =a.ord_mater_id  AND a.ord_sup_id = '"
				+ tMaterVo.getSupId()
				+ "'  AND b.ord_sup_id = '"
				+ tMaterVo.getSupId() + "'");
		sql.append(" GROUP BY a.ord_mater_id,a.ord_mater_level ORDER BY c.mater_type");
		return this.findByNativeQuery(sql.toString(), pageController);

	}

	public PaginationSupport listSupplier(PageController pageController, MaterVo tMaterVo) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.ord_mater_id, a.ord_mater_level, a.ord_sup_id, SUM(a.ord_mater_weight), c.mater_name, c.unit, c.specifications, FORMAT( (SELECT d.mater_value_undressed * 1000 FROM t_material_level_bl d WHERE d.mater_id = a.ord_mater_id AND d.sup_id = a.ord_sup_id AND d.mater_day = b.ord_date AND d.cust_id = b.ord_cust_id), 2 ), a.ord_mater_value, (SELECT describ FROM t_code WHERE code_type_id = 'mater_type' AND code_oder = c.mater_type) AS matertype, e.ord_mater_weight,c.brands,a.ord_id FROM t_order_supplier_mater_reality a, t_order_supplier b, t_material c, t_order_supplier_mater e WHERE a.ord_id = b.ord_id AND b.ord_date = '"
				+ tMaterVo.getDate()
				+ "' AND c.mater_id = a.ord_mater_id AND a.ord_sup_id = '"
				+ tMaterVo.getSupId()
				+ "' AND b.ord_cust_id = '"
				+ tMaterVo.getCustId()
				+ "' AND b.ord_sup_id = a.ord_sup_id AND e.ord_id = a.ord_id AND e.ord_mater_id = a.ord_mater_id AND e.ord_sup_id = a.ord_sup_id  GROUP BY a.ord_mater_id, a.ord_mater_level ORDER BY matertype ,a.ord_mater_id");
		return this.findByNativeQuery(sql.toString(), pageController);
	}

	public List<SupplierPo> getAllSup() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM SupplierPo p  ");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<DistributorSupplierMaterPo> getDistributorSupplierMaterPo(DistributorSupplierMaterVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();

		StringBuffer ql = new StringBuffer();
		ql.append(" select o from DistributorSupplierMaterPo o where 1=1 ");
		if (c != null) {
			if (c.getSupId() != 0) {
				ql.append(" and o.supId = '" + c.getSupId() + "' ");
			}
			if (c.getDistributorId() != 0) {
				ql.append(" and o.distributorId = '" + c.getDistributorId() + "' ");
			}
			if (c.getMaterId() != 0) {
				ql.append(" and o.materId = '" + c.getMaterId() + "' ");
			}
			if (c.getMaterType() != 0) {
				ql.append(" and o.materId IN (SELECT m.materId FROM MaterPo m WHERE m.materType = '" + c.getMaterType() + "')");
			}
		}

		List<DistributorSupplierMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<SupplierPo> getSupByMaterType(int materType) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM SupplierPo p WHERE EXISTS (SELECT 1 FROM SupplierTypePo t WHERE t.supId=p.supplierId AND t.materType ='"
				+ materType + "') ");
		List<SupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<Object[]> getMaterValueCostByMaterId(int materId, int supId) throws GenericException {

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT t2.mater_value*1000 FROM t_material m , (SELECT mc2.* FROM (SELECT MAX(mc.mater_day) AS a1, mc.mater_id AS a2 FROM t_material_level_cbl mc WHERE mc.sup_id = '"
				+ supId
				+ "' AND mater_id ='"
				+ materId
				+ "' AND mater_value != 0) AS t1, t_material_level_cbl mc2 WHERE mc2.mater_id = t1.a2 AND mc2.mater_day = t1.a1) t2 WHERE t2.mater_id = m.mater_id ");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

}
